const schedule = require('node-schedule');
const { executeQuery, formatDateTime } = require('./index');

// 定时检查过期的锁定期
const checkExpiredLocks = async () => {
    const now = new Date();
    const nDaysAgo = new Date(now);
    
    const selectLockDay = `
    SELECT data_value
    from utils
    WHERE utils_id = 2;
    `;
    const lockDatResult = await executeQuery(selectLockDay);
    const lockDay = lockDatResult[0]?.data_value;
    nDaysAgo.setDate(now.getDate() - lockDay);
    const formattednDaysAgo = nDaysAgo.toISOString().slice(0, 19).replace('T', ' ');

    const expiredQuery = `
        SELECT br.borrow_id, br.journal_id, br.lock_expiration, br.status, j.stock
        FROM borrowrecords br
        JOIN journals j ON br.journal_id = j.journal_id
        WHERE br.status = 'locked' AND br.borrow_date < ?;
    `;

    const updateBorrowRecord = `
        UPDATE borrowrecords
        SET status = 'cancelled'
        WHERE borrow_id = ?;
    `;

    const updateJournalStock = `
        UPDATE journals
        SET stock = stock + 1
        WHERE journal_id = ?;
    `;

    try {
        const expiredRecords = await executeQuery(expiredQuery, [formattednDaysAgo]);

        if (expiredRecords.length === 0) {
            return;
        }

        const updatePromises = expiredRecords.map(record => {
            return Promise.all([
                executeQuery(updateBorrowRecord, [record.borrow_id]),
                executeQuery(updateJournalStock, [record.journal_id])
            ]);
        });

        await Promise.all(updatePromises);
    } catch (error) {
        console.error('定时任务检查过期锁定期失败:', error);
    }
};

// 定时检查超期的借阅记录并生成罚款
const checkExpiredLoans = async () => {
    const now = new Date();  // 获取当前时间
    const oneDay = 1000 * 60 * 60 * 24; // 一天的毫秒数


    // 查询所有已经超期但未归还的借阅记录
    const overdueQuery = `
        SELECT br.borrow_id, br.user_id, br.journal_id, br.return_date, br.status
        FROM borrowrecords br
        WHERE br.status = 'borrowed' AND br.return_date < ?;
    `;

    // 查找现有罚款记录
    const findExistingFineRecord = `
        SELECT fine_id, fine_amount
        FROM fines
        WHERE borrow_id = ? AND user_id = ?;
    `;

    // 更新罚款记录
    const updateFineRecord = `
        UPDATE fines
        SET fine_amount = ?
        WHERE fine_id = ?;
    `;

    // 创建罚款记录
    const createFineRecord = `
        INSERT INTO fines (borrow_id, user_id, fine_amount)
        VALUES (?, ?, ?);
    `;

    // 更新用户的借阅权限（封禁）
    const updateUserBorrowLimit = `
        UPDATE users
        SET borrow_limit = 0
        WHERE user_id = ?;
    `;

    const selectFineIndex = `
        SELECT data_value
        from utils
        WHERE utils_id = 1;
    `;

    try {
        const overdueRecords = await executeQuery(overdueQuery, [now]);

        if (overdueRecords.length === 0) {
            return;
        }

        for (const record of overdueRecords) {
            const overdueTime = (now - new Date(record.return_date)) / oneDay;
            const fineIndexResult = await executeQuery(selectFineIndex);
            const fineIndex = fineIndexResult[0]?.data_value;
            const fineAmount = Math.ceil(overdueTime) * fineIndex;   
            const existingFineRecords = await executeQuery(findExistingFineRecord, [record.borrow_id, record.user_id]);
            if (existingFineRecords.length > 0) {
                const fineId = existingFineRecords[0].fine_id;
                await executeQuery(updateFineRecord, [fineAmount, fineId]);
            } else {
                await executeQuery(createFineRecord, [record.borrow_id, record.user_id, fineAmount]);
            }

            await executeQuery(updateUserBorrowLimit, [record.user_id]);
        }

    } catch (error) {
        console.error('定时任务检查过期借阅并生成罚款失败:', error);
    }
};

const addNextYearReservation = async () => {
    // 当前时间
    const now = formatDateTime(new Date());

    const selectNumOfMagazineQuery = `
        SELECT data_value
        FROM utils
        WHERE utils_id=3;
    `;

    NumOfMagazineResult = await executeQuery(selectNumOfMagazineQuery);
    NumOfMagazine = NumOfMagazineResult[0]?.data_value;
    
    // 查询借阅频率高的期刊
    const borrowFrequencyQuery = `
        SELECT journal_id, COUNT(*) AS borrow_count
        FROM borrowrecords
        WHERE borrow_date BETWEEN ? AND ?
        GROUP BY journal_id
        ORDER BY borrow_count DESC
        LIMIT ${NumOfMagazine};
    `;

    // 查询下一年度预定表中是否已存在记录
    const checkReservationQuery = `
        SELECT reservation_id, borrow_count, zero_stock_count, feedback_count
        FROM next_year_reservations
        WHERE journal_id = ? AND year = ? AND volume = ? AND issue = ?;
    `;

    // 更新已有记录
    const updateReservationQuery = `
        UPDATE next_year_reservations
        SET borrow_count = ?, zero_stock_count = ?, feedback_count = ?
        WHERE journal_id = ? AND year = ? AND volume = ? AND issue = ?;
    `;

    // 插入新记录
    const insertReservationQuery = `
        INSERT INTO next_year_reservations (journal_id, borrow_count, zero_stock_count, feedback_count, year, volume, issue, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?);
    `;

    // 查询用户反馈表中的反馈数据
    const userFeedbackQuery = `
        SELECT journal_id, COUNT(*) AS feedback_count
        FROM user_feedback
        WHERE journal_id IS NOT NULL
        GROUP BY journal_id;
    `;

    // 查询期刊的年卷期信息
    const journalInfoQuery = `
        SELECT journal_id, year, volume, issue
        FROM journals
        WHERE journal_id = ?;
    `;

    try {
        // 定义日期范围
        const startDate = '2024-01-01';
        const endDate = '2024-12-31';

        // 获取借阅频率高的期刊
        const borrowFrequency = await executeQuery(borrowFrequencyQuery, [startDate, endDate]);

        // 获取所有期刊的用户反馈数据
        const feedbackData = await executeQuery(userFeedbackQuery);

        // 合并期刊：将借阅频率高的期刊和用户反馈的期刊合并去重
        const allJournals = new Set(borrowFrequency.map(item => item.journal_id));
        feedbackData.forEach(item => allJournals.add(item.journal_id));

        if (allJournals.size === 0) {
            console.log('没有期刊需要更新');
            return;
        }

        // 遍历期刊列表，逐条处理
        for (const journal_id of allJournals) {
            // 获取当前期刊的借阅次数
            const borrowRecord = borrowFrequency.find(item => item.journal_id === journal_id);
            const borrow_count = borrowRecord ? borrowRecord.borrow_count : 0;

            // 获取当前期刊的用户反馈次数（直接使用反馈表中的计数值）
            const feedbackRecord = feedbackData.find(item => item.journal_id === journal_id);
            const feedback_count = feedbackRecord ? feedbackRecord.feedback_count : 0;

            // 获取期刊的年卷期信息
            const journalInfo = await executeQuery(journalInfoQuery, [journal_id]);
            if (journalInfo.length === 0) {
                continue; // 如果找不到期刊信息，跳过该期刊
            }

            const { year, volume, issue } = journalInfo[0];

            // 检查是否已存在于下一年度预定表中
            const existingReservation = await executeQuery(checkReservationQuery, [journal_id, year, volume, issue]);

            if (existingReservation.length > 0) {
                // 已存在记录，更新字段
                const currentZeroStockCount = existingReservation[0].zero_stock_count || 0;

                await executeQuery(updateReservationQuery, [
                    borrow_count,                // 更新借阅次数
                    currentZeroStockCount,       // 库存为 0 的次数保持不变
                    feedback_count,              // 更新用户反馈次数
                    journal_id,                  // 条件: journal_id
                    year,                        // 条件: 年
                    volume,                      // 条件: 卷
                    issue                        // 条件: 期
                ]);
            } else {
                // 不存在记录，插入新记录
                const zeroStockCount = 0;  // 初始化库存为 0 次
                const createdAt = now;     // 当前时间

                await executeQuery(insertReservationQuery, [
                    journal_id,
                    borrow_count,
                    zeroStockCount,
                    feedback_count,
                    year,    // 插入年
                    volume,  // 插入卷号
                    issue,   // 插入期号
                    createdAt
                ]);
            }
        }
    } catch (error) {
        console.error('定时任务更新下一年度预定表失败:', error);
    }
};




// 过期锁定检查
schedule.scheduleJob('* * * * * *', checkExpiredLocks);  // 每秒执行一次
// 不归还罚款检查
schedule.scheduleJob('* * * * * *', checkExpiredLoans);  // 每秒执行一次
// 新增下一年度预定表信息
schedule.scheduleJob('* * * * * *', addNextYearReservation);  // 每秒执行一次

// // 过期锁定检查
// schedule.scheduleJob('0 0 3 * * *', checkExpiredLocks);  // 每天凌晨 3:00 执行一次
// // 不归还罚款检查
// schedule.scheduleJob('0 0 0 * * *', checkExpiredLoans);  // 每天凌晨 12:00 检查一次
// // 新增下一年度预定表信息
// schedule.scheduleJob('0 0 0 * * 3', addNextYearReservation);  // 每周三凌晨 12:00 检查一次

module.exports = { checkExpiredLocks, checkExpiredLoans, addNextYearReservation };
